CREATE OR REPLACE PROCEDURE list_products_dynamic 
(p_product_name VARCHAR2 DEFAULT NULL) 
AS
  TYPE cv_prodtyp IS REF CURSOR;
  cv   cv_prodtyp;
  v_prodname product_information.product_name%TYPE;
  v_minprice product_information.min_price%TYPE;
  v_listprice product_information.list_price%TYPE;
  v_stmt  VARCHAR2(400);
BEGIN
  v_stmt := 'SELECT product_name, min_price, list_price 
             FROM product_information WHERE product_name LIKE
             ''%'||p_product_name||'%''';
  OPEN cv FOR v_stmt;
  dbms_output.put_line(v_stmt);
  LOOP
    FETCH cv INTO v_prodname, v_minprice, v_listprice;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Product Info: '||v_prodname ||', '||
                          v_minprice ||', '|| v_listprice);
  END LOOP;
  CLOSE cv;
END;
/
